package model.dba;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;

import model.Estado_Recado;
import model.Recado;
import model.Usuario;

public class RecadoDAO extends JDBC {
	private static RecadoDAO recadoDAO;

	private RecadoDAO() {
		super();
	}

	public static RecadoDAO getInstance() {
		if (recadoDAO == null) {
			recadoDAO = new RecadoDAO();
		}
		return recadoDAO;
	}

	public int add(Recado recado) {

		try {
			String queryString = "INSERT INTO recados(emisor, receptor, estado, mensaje, fecha,  asunto, borradoemisor, borradoreceptor)" + " VALUES(?, ?, ?, ?, ?, ?,?,?) ";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setLong(1, recado.getEmisor().getId());
			preparedStatement.setLong(2, recado.getReceptor().getId());
			preparedStatement.setString(3, recado.getEstado().toString());
			preparedStatement.setString(4, recado.getMensaje());
			preparedStatement.setTimestamp(5, new Timestamp(recado.getFecha().getTimeInMillis()));
			preparedStatement.setString(6, recado.getAsunto());
			preparedStatement.setString(7, recado.getborradoEmisor());
			preparedStatement.setString(8, recado.getborradoReceptor());


			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return getLastId();

	}

	public boolean update(Recado recado) {

		boolean state = false;
		try {
			String queryString = "UPDATE recados SET  emisor=?, receptor=?, estado=?, mensaje=?, fecha=?, borradoemisor=?, borradoreceptor=?, asunto=? WHERE idRecado=?";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setLong(1, recado.getEmisor().getId());
			preparedStatement.setLong(2, recado.getReceptor().getId());
			preparedStatement.setString(3, recado.getEstado().toString());
			preparedStatement.setString(4, recado.getMensaje());
			preparedStatement.setTimestamp(5, new Timestamp(recado.getFecha().getTimeInMillis()));
			preparedStatement.setString(6, recado.getborradoEmisor());
			preparedStatement.setString(7, recado.getborradoReceptor());
			preparedStatement.setString(8, recado.getAsunto());

			preparedStatement.setLong(9, recado.getId());
			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return state;
	}

	public boolean delete(Recado recado) {
		boolean state = false;

		try {
			String queryString = "DELETE FROM recados WHERE idRecado=?";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setInt(1, recado.getId());
			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return state;

	}

	public Recado get(Integer id) {

		Recado recado = new Recado();
		try {
			String queryString = "SELECT * FROM recados where idRecado = '" + id + "';";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				recado = populate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return recado;
	}

	public int getLastId() {
		int ret = 0;
		try {
			String queryString = "SELECT idRecado FROM recados order by idRecado DESC LIMIT 1  ";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				ret = resultSet.getInt("idRecado");

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return ret;

	}

	public List<Recado> getAll() {
		List<Recado> ret = new ArrayList<Recado>();

		try {
			String queryString = "SELECT * FROM recados ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				ret.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return ret;
	}

	public List<Recado> get(Usuario emisor, Usuario receptor) {
		List<Recado> ret = new ArrayList<Recado>();

		try {
			String queryString = "SELECT * FROM recados where emisor= " + emisor.getId() + " and receptor=" + receptor.getId() + " ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				ret.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return ret;
	}

	public List<Recado> get(Usuario emisor, Estado_Recado estado) {
		List<Recado> ret = new ArrayList<Recado>();

		try {
			String queryString = "SELECT * FROM recados where emisor= " + emisor.getId() + " and estado='" + estado.toString() + "' ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				ret.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return ret;
	}

	public List<Recado> get(Estado_Recado estado, Usuario receptor) {
		List<Recado> ret = new ArrayList<Recado>();

		try {
			String queryString = "SELECT * FROM recados where estado='" + estado.toString() + "' and receptor=" + receptor.getId() + " ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				ret.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return ret;
	}

	public List<Recado> get(Estado_Recado estado) {
		List<Recado> ret = new ArrayList<Recado>();

		try {
			String queryString = "SELECT * FROM recados where estado='" + estado.toString() + "' ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				ret.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return ret;
	}

	private Recado populate() throws SQLException {
		UsuarioDAO usuarioDAO = UsuarioDAO.getInstance();

		Recado recado = new Recado();
		recado.setAsunto(resultSet.getString("asunto"));
		recado.setEmisor(usuarioDAO.get((int) resultSet.getLong("emisor")));// emisor
		recado.setEstado(Estado_Recado.valueOf(resultSet.getString("estado").equals("no leido") ? "No_Leido" : "Leido"));

		GregorianCalendar fecha = new GregorianCalendar();
		fecha.setTime(resultSet.getTimestamp("fecha"));
		recado.setFecha(fecha);

		recado.setId((int) resultSet.getLong("idRecado"));
		recado.setMensaje(resultSet.getString("mensaje"));
		recado.setReceptor(usuarioDAO.get((int) resultSet.getLong("receptor")));// receptor
		recado.setborradoEmisor(resultSet.getString("borradoemisor"));
		recado.setborradoReceptor(resultSet.getString("borradoreceptor"));

		return recado;

	}
}